package encomendaFacil.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import encomendaFacil.bean.Package;

public class PackageDAO implements IPackageDAO{

	public static final String UPDATE_STATUS = "update_status";
	public static final String UPDATE_DELIVERED = "is_delivered";
	public static final String GENERIC_UPDATE = "";
	
	private static final Logger logger = Logger.getLogger(PackageDAO.class);
	
	@Override
	public Package insert(Package pack) {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		String sql = Queries.PACKAGE_INSERT;
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, pack.getTrackingCode());
			ps.setString(2, pack.getName());
			ps.setString(3, pack.getStatus());
			ps.setBoolean(4, false);
			ps.setLong(5, pack.getOwner().getId());
			
			rs = ps.executeQuery();
			
			if (rs.next()) {
				pack.setId(rs.getLong("1"));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				conn.close();
				rs.close();
				ps.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		}
		
		return pack;
	}

	@Override
	public void remove(long id) {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		String sql = Queries.PACKAGE_REMOVE;
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(0, id);
			ps.executeQuery();
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				conn.close();
				ps.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		
		}
		
	}

	@Override
	public boolean updateDelivered(Package pack){
		Connection conn = ConnectionFactory.getInstance().getConnection();
		String sql = Queries.PACKAGE_UPDATE_DELIVERED;
		PreparedStatement ps = null;
		int columnsAffected = 0;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, pack.getStatus());
			ps.setBoolean(2, pack.isDelivered());
			ps.setLong(3, pack.getId());
			
			columnsAffected = ps.executeUpdate();
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				conn.close();
				ps.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		}
		
		return columnsAffected == 3 ? true : false;
		
	}
	
	@Override
	public boolean updateStatus(Package pack){
		Connection conn = ConnectionFactory.getInstance().getConnection();
		String sql = Queries.PACKAGE_UPDATE_STATUS;
		PreparedStatement ps = null;
		int columnsAffected = 0;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, pack.getStatus());
			ps.setLong(2, pack.getId());
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				conn.close();
				ps.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		}
		
		return columnsAffected == 2 ? true : false;
	}

	@Override
	public Package findOne(String query) {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		Package pack = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			
			if (rs.next()) {
				pack = new Package();
				pack.setId(rs.getLong(ColumnIndexes.PACKAGE_ID));
				pack.setBeginDate(rs.getDate(ColumnIndexes.BEGIN_DATE));
				pack.setDelivered(rs.getBoolean(ColumnIndexes.IS_DELIVERED));
				pack.setLastModification(rs.getDate(ColumnIndexes.LAST_MODIFICATION));
				pack.setName(rs.getString(ColumnIndexes.PACKAGE_NAME));
				pack.setOwner(DAOFactory.getInstance().getUserDAO().findById(rs.getLong(ColumnIndexes.OWNER_ID)));
				pack.setStatus(rs.getString(ColumnIndexes.STATUS));
				pack.setTrackingCode(rs.getString(ColumnIndexes.TRACKING_CODE));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally{
			try {
				conn.close();
				stmt.close();
				rs.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		}
		
		return pack;
	}

	@Override
	public List<Package> find(String query) {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List<Package> packs = new ArrayList<Package>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			
			while (rs.next()) {
				Package pack = new Package();
				pack.setId(rs.getLong(ColumnIndexes.PACKAGE_ID));
				pack.setBeginDate(rs.getDate(ColumnIndexes.BEGIN_DATE));
				pack.setDelivered(rs.getBoolean(ColumnIndexes.IS_DELIVERED));
				pack.setLastModification(rs.getDate(ColumnIndexes.LAST_MODIFICATION));
				pack.setName(rs.getString(ColumnIndexes.PACKAGE_NAME));
				pack.setOwner(DAOFactory.getInstance().getUserDAO().findById(rs.getLong(ColumnIndexes.OWNER_ID)));
				pack.setStatus(rs.getString(ColumnIndexes.STATUS));
				pack.setTrackingCode(rs.getString(ColumnIndexes.TRACKING_CODE));
				packs.add(pack);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally{
			try {
				conn.close();
				stmt.close();
				rs.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		}
		return packs;
	}

}
